-- demonstration to show that releasing the memory after deleting elements
-- is beneficial.
-- Courtesy of Laszlo Czinkoczki

DECLARE 
  TYPE NumTab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER; 
  TYPE NameTab IS TABLE OF CHAR(30) INDEX BY BINARY_INTEGER; 
  pnums  NumTab; pnames NameTab; 

  PROCEDURE print_pga(text VARCHAR2) 
  IS 
    pga_size NUMBER; 
  BEGIN 
    SELECT value into pga_size FROM v$pgastat where name='total PGA inuse'; 
    DBMS_OUTPUT.PUT_LINE 
      ('pga_size ='||to_char(pga_size,'999,999,999')||text); 
  END; 

  BEGIN -- main
    print_pga(' before allocation');   
    FOR j IN 1..150000 LOOP  
      pnums(j):= j;  pnames(j):= 'Part No.'||TO_CHAR(j); 
    END LOOP; 
    print_pga(' after  allocation');     
    pnums.delete(1,150000); pnames.delete(1,150000); 
    print_pga(' after  DELETE'); 
    DBMS_SESSION.FREE_UNUSED_USER_MEMORY; 
    print_pga(' after  FREE_UNUSED_USER_MEMORY'); 
END; 
/ 